/** ************************
 * Project: ePoverty
 * Filename: TableMaker.java
 * Description: Creates a generic Object table from a single query.
 * Name: Bunna Veth
 * Date: Apr 10, 2012
 ************************** */
package epoverty;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class TableMaker
{
    public static SortableTable createTable(String query)
    {
        //initialize variables
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        SortableTable table = null;

        try
        {
            //establish connection and execute query
            connection = DBconnection.getConnection();
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            resultSet = statement.executeQuery(query);

            //get table dimensions
            ResultSetMetaData metaData = resultSet.getMetaData();
            int numberOfColumns = metaData.getColumnCount();
            resultSet.last(); //go to the end to retrieve row number (in order to initialize array)
            int numberOfRows = resultSet.getRow();
            Object[][] tableData = new Object[numberOfRows][numberOfColumns];

            //column names
            String[] columnNames = new String[numberOfColumns];
            String[] columnClasses = new String[numberOfColumns];
            for (int i = 0; i < numberOfColumns; i++)
            {
                columnNames[i] = metaData.getColumnName(i + 1);
                columnClasses[i] = metaData.getColumnClassName(i + 1);
            }

            //populate table data
            int row = 0;
            resultSet.beforeFirst(); //move cursor back to the start
            while (resultSet.next())
            {
                for (int i = 0; i < numberOfColumns; i++)
                    tableData[row][i] = resultSet.getObject(i + 1); //result set starts counting at 1
                row++;
            }

            //construct table
            ObjectTableModel tableModel = new ObjectTableModel(tableData, columnNames, columnClasses);
            table = new SortableTable(tableModel);
        }
        catch (Exception e)
        {
            System.err.println("Unable to create table.");
        }
        finally
        {
            //close connections
            try
            {
                if (connection != null) connection.close();
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            }
            catch (SQLException e)
            {
                System.err.println("Unable to close connections.");
            }
        }

        //return table
        return table;
    }
}//end class